# -*- coding: UTF-8 -*-
from dateutil.rrule import rrule, DAILY
from openpyxl import Workbook
from openpyxl.styles import Font
from openpyxl.comments import Comment
from pathlib import Path
import sys, getopt, datetime, copy, calendar, pickle, json, os

default_name = "未知"
null_record = "无记录"
idCol = 1  # 数据中，工号所在的列
onDuty_time = datetime.datetime.strptime("8:30:00", "%H:%M:%S")
offDuty_time = datetime.datetime.strptime("17:30:00", "%H:%M:%S")
commentAuthor = "XX研发部"
defautDictFilename = os.getcwd() + "\\UserDict.txt"

red_fill = Font(color="FF0000")
green_fill = Font(color="00FF00")
normal_fill = Font(color="000000")

weekday_name = ("周一", "周二", "周三", "周四", "周五", "周六", "周天")

# 问题记录
# class ErrorStr(Enum):
ErrorStr_OK = "正常"
ErrorStr_WEEKEND = "周末"
ErrorStr_ONDUTYERR = "上班卡异常"
ErrorStr_OFFDUTYERR = "下班卡异常"
ErrorStr_NORECORD = "全天缺卡"
ErrorStr_OTHER = "未知问题"

def GetUserId(fileName):
    d = {}
    if fileName == defautDictFilename:
        userFile = Path(fileName)
        if not userFile.exists():
            d = {
                1: "许xx",
                2: "高xx",
                3: "谭xx",
                4: "张xx",
                5: "高x",
                6: "杨x",
                7: "姚x",
                8: "谢xx",
                9: "王xx",
                10: "余x",
                11: "孙x",
                12: "申x",
                13: "赵x",
                14: "熊xx",
            }

            f = open(fileName, 'w')
            json.dump(d, f, ensure_ascii=False)
            return d

    with open(fileName, "r") as f:
        d = json.load(f)

    return d


# 重原始数据中逐行提取数据
def ParseData(fileName, dictFileName):
    # 打开一个文件
    f = open(fileName, "r+")
    lines = f.readlines()  # 调用文件的 readline()方法
    f.close()

    puOutLines = []
    userDict = GetUserId(dictFileName)

    for line in lines:
        pLine = [x for x in line.split('\t') if x]
        if pLine[0].strip() in userDict:
            pLine.insert(0, userDict[pLine[0].strip()])
        elif int(pLine[0]) in userDict:
            pLine.insert(0, userDict[int(pLine[0])])
        else:
            pLine.insert(0, "未知")
        puOutLines.append(pLine)

    return puOutLines


# 计算最大和最小的打卡日期间有多少天，以及周几
def ToDate(dataLines):
    endDate = datetime.datetime.strptime("2000-01-01 00:00:00", "%Y-%m-%d %H:%M:%S")
    startDate = datetime.datetime.strptime("2090-01-01 00:00:00", "%Y-%m-%d %H:%M:%S")
    rows = len(dataLines)
    for curRow in range(0, rows):
        # 先转换为时间数组,然后转换为其他格式
        thisDataTime = datetime.datetime.strptime(dataLines[curRow][2], "%Y-%m-%d %H:%M:%S")
        if thisDataTime < startDate:
            startDate = thisDataTime

        if thisDataTime > endDate:
            endDate = thisDataTime

    print(startDate, endDate)
    datetimeList = []
    for dt in rrule(DAILY, dtstart=startDate, until=endDate):
        whichday = calendar.weekday(dt.year, dt.month, dt.day)
        datetimeList.append((dt, whichday))

    return datetimeList


# 获取列表的员工工号
def takeId(elem):
    return elem[1]


def takeDatetime(elem):
    return elem[2]


# #提取当天的打卡记录，并且删除原始记录，避免重复计算
# #默认所有数据是同一个人的
def getRecordInThisDay(datas, dateObj):
    sameDay = []
    for perDay in datas:
        lineDate = datetime.datetime.strptime(perDay[2], '%Y-%m-%d %H:%M:%S')
        if lineDate.date() == dateObj[0].date():
            sameDay.append(lineDate)

    if len(sameDay) == 0:
        sameDay.append(null_record)

    return sameDay


# 处理每个人的打卡情况
# 输入的数据是已经按工号分开的
def statistTime(splitList, dayList):
    allRecord = []
    oneRecord = []
    nameList = []
    for items in splitList:
        for oneDay in dayList:
            oneRecord.append(getRecordInThisDay(items, oneDay))

        if len(oneRecord) != 0:
            nameList.append(items[0][0])
            allRecord.append(copy.deepcopy(oneRecord))
            oneRecord.clear()

    return allRecord, nameList


# #按工号来排序打卡记录，并分割出来
def splitWithId(dataLines):  # 这个函数会漏数据
    newList = copy.deepcopy(dataLines)
    newSub = []
    retList = []
    # 指定第二个元素排序
    newList.sort(key=takeId)
    for line in newList:
        if len(newSub) == 0:
            newSub.append(line)
        elif line[idCol] == newSub[0][idCol]:
            newSub.append(line)
        else:
            retList.append(copy.deepcopy(newSub))
            newSub.clear()

    retList.append(copy.deepcopy(newSub))

    return retList


def splitWithId_v2(dataLines):
    idDict = {}
    retList = []

    for oneLine in dataLines:
        idDict.setdefault(oneLine[idCol], []).append(oneLine)

    for oneList in idDict.values():
        retList.append(oneList)

    return retList


# 将打卡记录转换为文字信息
# 判断打卡时间是否合规
def IsRecordOnTime(record):
    retStr = ""
    tips = ""
    retFill = normal_fill
    onDutyPass = False
    offDutyPass = False

    if len(record) != 0:
        if record[0] == null_record:
            tips = ErrorStr_NORECORD+";"
            return "", tips, red_fill

        else:
            for dateObj in record:
                if isinstance(dateObj, datetime.datetime):
                    if len(retStr) == 0:
                        retStr += dateObj.strftime('%H:%M:%S')
                    else:
                        retStr += dateObj.strftime('\n%H:%M:%S')

                    if dateObj.time() < onDuty_time.time():
                        onDutyPass = True
                    elif dateObj.time() > offDuty_time.time():
                        offDutyPass = True
                else:
                    tips += ErrorStr_OTHER + ";"
    else:
        tips = ErrorStr_OTHER + ";"
        return "", tips, red_fill

    if not onDutyPass:
        tips += ErrorStr_ONDUTYERR + ";"

    if not offDutyPass:
        tips += ErrorStr_OFFDUTYERR + ";"

    if onDutyPass and offDutyPass:
        retFill = green_fill
    else:
        retFill = red_fill

    return retStr, tips, retFill


# 判断打卡记录是否合规
# #输入的打卡记录，只能是
def JudgeRecord(record, thisDay):
    retStr, tips, retFill = IsRecordOnTime(record)

    if thisDay[1] == 5 or thisDay[1] == 6:  # 假设是周末,周末不判断
        tips += ErrorStr_WEEKEND + ";"
        retFill = normal_fill

    return retStr, tips, retFill


# 解析指定的考勤数据（dataFileName），保存到execl中（execlFileName）
def WriteToExecl(dataFileName, execlFileName, dictFileName):
    dataLines = ParseData(dataFileName, dictFileName)
    dayList = ToDate(dataLines)  # 返回日期和周几
    splitList = splitWithId_v2(dataLines)
    allRecord, nameList = statistTime(splitList, dayList)

    rows = len(dataLines)
    if rows != 0:
        cols = len(dataLines[0])

        wb = Workbook()
        ws = wb.create_sheet(title="考勤原始数据")

        for curRow in range(0, rows):
            for curCol in range(0, cols):
                _ = ws.cell(column=curCol + 1, row=curRow + 1, value=dataLines[curRow][curCol])

        # #开始统计数据
        recordRows = len(allRecord)
        if recordRows != 0:
            recordCols = len(allRecord[0])

            ws2 = wb.create_sheet(title="考勤统计")
            wb.active = ws2

            # #添加行标头-日期
            for curRecordCol in range(0, recordCols):
                # ws2.column_dimensions[curRecordCol].auto_size = True
                ws2.cell(column=curRecordCol + 2, row=1, value=weekday_name[dayList[curRecordCol][1]])
                ws2.cell(column=curRecordCol + 2, row=2, value=dayList[curRecordCol][0].strftime('%Y/%m/%d'))

            # #添加列标头-名字
            for curRecordRow in range(0, recordRows):
                # ws2.row_dimensions[curRecordRow].auto_size = True
                ws2.cell(column=1, row=curRecordRow + 3, value=nameList[curRecordRow])

            for curRecordRow in range(0, recordRows):
                for curRecordCol in range(0, recordCols):
                    # #考勤数据
                    cellValue, tips, cellFill = JudgeRecord(allRecord[curRecordRow][curRecordCol],
                                                            dayList[curRecordCol])
                    thisCell = ws2.cell(column=curRecordCol + 2, row=curRecordRow + 3, value=cellValue)
                    thisCell.font = cellFill
                    if cellFill != green_fill:
                        thisCell.comment = Comment(tips, commentAuthor)

        wb.save(execlFileName)


def main(argv):
    inputfile = ''
    outputfile = ''
    dictfile = defautDictFilename
    try:
        opts, args = getopt.getopt(argv, "hi:o:d:", ["ifile=", "ofile=", "dict="])
    except getopt.GetoptError:
        print('ToExecl.py -i <inputfile> -o <outputfile> -d <userdict>')
        sys.exit(2)
    for opt, arg in opts:
        if opt == '-h':
            print('ToExecl.py -i <inputfile> -o <outputfile> -d <userdict>')
            sys.exit()
        elif opt in ("-i", "--ifile"):
            inputfile = arg
        elif opt in ("-o", "--ofile"):
            outputfile = arg
        elif opt in ("-d", "--dict"):
            dictfile = arg

    if len(inputfile) != 0 and len(outputfile) != 0:
        print("WriteToExecl -i",inputfile, " -o" ,outputfile, " -d", dictfile )
        WriteToExecl(inputfile, outputfile, dictfile)

    print("End" )


if __name__ == "__main__":
    main(sys.argv[1:])
